IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProcedurePrices]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetProcedurePrices]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/***************************************************************************
* Name:			GetProcedurePrices
* Purpose:		Get's the procedure prices.
* exec GetProcedurePrices  @Institution='BWH',@department='506799', @CPTcode='99201',@description='NEW PT BRIEF VISIT',@location='SOUTHERN JAMAICA PLAIN H C',@IsNonFacility=1,@SponsorType='Industry'
* exec GetProcedurePrices  @Institution='MGH', @CPTcode='99201',@department='MG5282',@IsNonFacility=0,@SponsorType='Industry'
* exec GetProcedurePrices  @Institution='BWH', @CPTcode='77421',@department='506656',@Chargecode=6561120,@SponsorType='Government',@IsNonFacility=1
* exec GetProcedurePrices  @Institution='MGH', @CPTcode='77055',@department='MG7667',@SponsorType='Government'
* exec GetProcedurePrices   @Institution='MGH', @CPTcode='77055',@IsNonFacility=1
* 
* exec GetProcedurePrices  @Institution='bwH', @techprocode='p'
exec GetProcedurePrices @Institution='BWH',@chargeCode=3005998,@SponsorType='Government',@IsNonFacility=1
exec getclinicaltrialbudgetfeeddata @institution='ClinicalTrialBudgetInstitutionBWH',@sponsorType='Industry'
exec getclinicaltrialbudgetfeeddata @institution='ClinicalTrialBudgetInstitutionMGH',@sponsorType='Industry'
exec GetProcedurePrices  @Institution='BWH',@cptcode='99201',@department='506656',@sponsorType='Government',@IsNonFacility=0
exec GetProcedurePrices   @Institution='BWH', @CPTcode='99203',@location = 'BROOKSIDE PARK F L C'
exec GetProcedurePrices  @Institution='ClinicalTrialBudgetInstitutionMGH', @department= 'MG5261', @CPTcode='77055', @location = 'ACC MINOR IMAGING',@IsNonFacility=1
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
 @Institution		varchar(50)	= null
,@ChargeCode		int			= null
,@CPTcode			varchar(6)	= null
,@Description		varchar(50)	= null
,@Department		varchar(10)	= null
,@Location			varchar(50)	= null
,@SponsorType		Varchar(256)= Null
,@IsNonFacility		bit			= null
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
Create Procedure dbo.GetProcedurePrices(
	 @Institution	varchar(50)	= null
	,@ChargeCode	int			= null
	,@CPTcode		varchar(50)	= null
	,@Department	varchar(50)	= null
	,@Location		varchar(50)	= null
	,@Description	varchar(50)	= null
	,@SponsorType	Varchar(256) = Null
	,@IsNonFacility	bit = null
	,@UsePOPricing	bit = null
)
AS
Begin

set nocount on
set @Institution	= isnull(right(@Institution,3),'')
set @ChargeCode		= isnull(@ChargeCode,0)
set @CPTcode		= isnull(@CPTcode,'')
set @Department		= isnull(@Department,'')
set @Location		= isnull(@Location,'')
set @Description	= isnull(@Description,'')
set @IsNonFacility	= isnull(@IsNonFacility,0)
set @UsePOPricing	= isnull(@UsePOPricing,0)
set @SponsorType	= IsNull(@SponsorType,'Industry')
if @SponsorType like '%Industry' set @SponsorType = 'Industry' 
if @SponsorType like '%Government' set @SponsorType = 'Government' 
if @SponsorType != 'Government' and @SponsorType != 'Industry' set @SponsorType = 'Industry' 

	/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
						P R I C E  R E S U L T S E T
	CPT Table variable is used to join the BWH_CDMFEED, MGH_CDMFEED and CHARGEMASTER
	tables on CPTcode and department.  The resultset will contain a single row for
	each matching CPTcode, Department, Description, Location, and ChargeCode.
	Depending on an available Tech/Pro/Global fee row, each column will
	contain data from the Tech row or if not there, the pro-fee row, or if
	not there, either blank or the chargemaster row.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

	Select Distinct
		p.Institution, p.Dept, p.Description, p.Price, p.ChargeCode, p.CPTCode
		, p.ResearchRate, p.GovernmentRate, p.IndustryRate, p.GovernmentCharge, p.IndustryCharge
		, Case when @Institution = 'BWH' and @SponsorType = 'Industry' then p.IndustryCharge
				when @Institution = 'BWH' and @SponsorType <> 'Industry' then p.GovernmentCharge
				when @Institution = 'MGH' then p.ResearchCharge
				Else p.ResearchCharge
			End as 'ResearchCharge'
		, p.ProFee
		,p.GlobalFee as POGlobalFee
		,p.POTechFee as POTechFee
		,p.POProFee as POProFee
		,p.NFGlobalFee
		,p.NFPOTechFee
		,p.NFPOProFee
		, p.Location
	From dbo.ProcedurePickerPrices p
	where		p.Institution = @Institution
				and p.chargecode <> 0
				and p.cptcode like rtrim(ltrim(@cptcode))+'%' 
				and (p.dept like ltrim(rtrim(@Department))+'%')
				and coalesce(p.description,'') like ltrim(rtrim(@Description))+'%'
				and (coalesce(p.location,'') like ltrim(rtrim(@Location))+'%' )
				and (coalesce(p.chargecode,0) = @chargecode  or @chargecode = 0)
				and (@UsePOPricing = 1 or p.chargeCode > 0)
	order by p.dept, p.cptcode
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
